Imports System.Data.SqlClient

Public Class FunctionsSecurity

    Shared Function GetActiveAccountNamebyEmail(ByVal strEmail As String) As String
        Dim conDB = Connections.GetSQLConnection
        Dim cmdSelect As SqlCommand
        Dim dtrRoles As SqlDataReader
        Dim strSql As String = ""
        Dim strUID As String = ""

        strSql = "select account_Name, account_status from Members where emailaddress = @parEmail and account_name <> '' ;"

        cmdSelect = New SqlCommand(strSql, conDB)
        cmdSelect.Parameters.Add("@parEmail", SqlDbType.NVarChar).Value = strEmail
        conDB.open()
        'Determine this user's roles
        dtrRoles = cmdSelect.ExecuteReader()

        Do While dtrRoles.Read()
            strUID = (dtrRoles("Account_Name"))
        Loop

        conDB.close()

        Return (strUID)
    End Function
    Shared Function ReturnMemberID_byEmail(ByVal strEmail As String) As String

        Dim conDB = Connections.GetSQLConnection
        Dim cmdSelect As SqlCommand
        Dim dtrRoles As SqlDataReader
        Dim strSql As String = ""
        Dim strUID As String = ""

        strSql = "select  Member_Id from Members where emailaddress = @parEmail ;"

        cmdSelect = New SqlCommand(strSql, conDB)
        cmdSelect.Parameters.Add("@parEmail", SqlDbType.NVarChar).Value = strEmail
        conDB.open()
        'Determine this user's roles
        dtrRoles = cmdSelect.ExecuteReader()

        Do While dtrRoles.Read()
            strUID = (dtrRoles("Member_Id"))
        Loop

        conDB.close()

        Return (strUID)
    End Function
    Shared Function GetAffiliateby_MemberID(ByVal strMemberID As String) As String

        Dim conDB = Connections.GetSQLConnection
        Dim cmdSelect As SqlCommand
        Dim dtrRoles As SqlDataReader
        Dim strSql As String = ""
        Dim strUID As String = ""

        strSql = "select Affiliate_ID from Members where Member_Id = @parUID ;"

        cmdSelect = New SqlCommand(strSql, conDB)
        cmdSelect.Parameters.Add("@parUID", SqlDbType.NVarChar).Value = strMemberID
        conDB.open()
        'Determine this user's roles
        dtrRoles = cmdSelect.ExecuteReader()

        'assign some information about the user to session
        'Dim objFName As HttpCookie
        'Dim objRoleName As HttpCookie

        Do While dtrRoles.Read()
            strUID = (dtrRoles("Affiliate_ID"))
        Loop

        conDB.close()

        Return (strUID)

    End Function
    Shared Function ReturnPaidMembershipId_byEmail(ByVal strEmail As String) As String
        'todo: complex function, rewrite so it is easier to read
        'returns 0 when the user has never applied
        'returns -1 if the user has never paid

        Dim conDB = Connections.GetSQLConnection
        Dim cmdSelect As SqlCommand
        Dim dtrRoles As SqlDataReader
        Dim strSql As String = ""
        Dim strUID As String = ""
        Dim dtPaid As Date

        strSql = "select account_Name, account_status, Member_ID, Date_Paid, CS_UserID, CS_UserName, Date_Expired " & _
        " from Members where emailaddress = @parEmail;"

        cmdSelect = New SqlCommand(strSql, conDB)
        cmdSelect.Parameters.Add("@parEmail", SqlDbType.NVarChar).Value = strEmail
        conDB.open()
        'Determine this user's roles
        dtrRoles = cmdSelect.ExecuteReader()

        If Not dtrRoles.HasRows Then
            Return (0)
        End If
        Do While dtrRoles.Read()
            strUID = (dtrRoles("Member_ID"))
            'strDatePaid = (dtrRoles("Date_Paid"))
            dtPaid = CType(dtrRoles("Date_Paid"), Date)
            If dtPaid < "01/01/2004" Then
                Return (-1)
            End If
        Loop

        conDB.close()

        Return (strUID)
    End Function
    Shared Function ReturnCommunityServerUserId(ByVal intMemberId As Integer)
        Dim conDB = Connections.GetSQLConnection
        Dim cmdSelect As SqlCommand
        Dim dtrRoles As SqlDataReader
        Dim strSql As String
        Dim intCommunityServerId As Integer

        strSql = "select CS_UserID, CS_UserName " & _
        " from Members where member_ID = @parMemberID;"

        cmdSelect = New SqlCommand(strSql, conDB)
        cmdSelect.Parameters.Add("@parMemberID", SqlDbType.NVarChar).Value = intMemberId
        conDB.Open()
        'Determine this user's roles
        dtrRoles = cmdSelect.ExecuteReader()

        If Not dtrRoles.HasRows Then
            Return (0)
        End If

        Do While dtrRoles.Read()
            intCommunityServerId = CType((dtrRoles("CS_UserID")), Integer)
        Loop
        Return intCommunityServerId
    End Function

    Shared Sub DeleteMemberIDby_MemberID(ByVal strMemberID As String)

        Dim conDB = Connections.GetSQLConnection
        Dim cmdSelect As SqlCommand
        Dim strSql As String

        strSql = "delete from Members where Member_Id = @parUID ;"

        cmdSelect = New SqlCommand(strSql, conDB)
        cmdSelect.Parameters.Add("@parUID", SqlDbType.NVarChar).Value = strMemberID
        conDB.open()
        'Determine this user's roles
        cmdSelect.ExecuteNonQuery()

        'assign some information about the user to session
        'Dim objFName As HttpCookie
        'Dim objRoleName As HttpCookie

        conDB.close()

    End Sub

End Class
